package com.zjl.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;

import com.zjl.entity.StudentEntity;
import com.zjl.util.DBManager;
import com.zjl.util.QueryResult;

public class StudentDaoImpl {
      public ArrayList<StudentEntity> selectAll(String name) {
		ArrayList<StudentEntity> alist=new ArrayList<StudentEntity>();
		String sqlString;
	    if (name!=null&&!name.equals("")) {
	      sqlString="select stu_id,stu_name,sex,mtel,qq,address,class_name from students where stu_name like '%"+name+"%' ORDER BY stu_id desc";
	    }else {
	       sqlString="select stu_id,stu_name,sex,mtel,qq,address,class_name from students ORDER BY stu_id desc";
	    }
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	            alist.add(new StudentEntity(rSet.rs.getInt("stu_id"),
	            		rSet.rs.getString("stu_name"),
	            		rSet.rs.getString("sex"),
	            		rSet.rs.getString("mtel"),rSet.rs.getString("qq"),
	            		rSet.rs.getString("address"),rSet.rs.getString("class_name")));
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
		return alist;
	}
      public StudentEntity userDetail(int id) {
    	  StudentEntity userBean= null;
    	  String sqlString="select stu_id,stu_name,sex,mtel,qq,address,class_name from students where stu_id='"+id+"'";
  	    QueryResult rSet=DBManager.executeQuery(sqlString);
  	      if (rSet!=null) {
  	        try {
  	          while (rSet.rs.next()) {
  	        	userBean=new StudentEntity(rSet.rs.getInt("stu_id"),
  	            		rSet.rs.getString("stu_name"),
  	            		rSet.rs.getString("sex"),
  	            		rSet.rs.getString("mtel"),rSet.rs.getString("qq"),
  	            		rSet.rs.getString("address"),rSet.rs.getString("class_name"));
  	          }
  	        } catch (SQLException e) {
  	          e.printStackTrace();
  	        }
  	      }
  	      rSet.close();
  		return userBean;
  	}
      public StudentEntity selectLastStudent() {
  		StudentEntity sEntity=null;
  		String sqlString="select stu_id,stu_name,sex,mtel,qq,address,class_name " + 
  				"from students ORDER BY stu_id desc limit 1";
  		 QueryResult rSet=DBManager.executeQuery(sqlString);
  	      if (rSet!=null) {
  	        try {
  	          while (rSet.rs.next()) {
  	        	 sEntity=new StudentEntity(rSet.rs.getInt("stu_id"),
  	            		rSet.rs.getString("stu_name"),
  	            		rSet.rs.getString("sex"),
  	            		rSet.rs.getString("mtel"),rSet.rs.getString("qq"),
  	            		rSet.rs.getString("address"),rSet.rs.getString("class_name"));
  	          }
  	        } catch (SQLException e) {
  	          e.printStackTrace();
  	        }
  	      }
  	      rSet.close();
  		return sEntity;
  	}
      public int studentAdd(StudentEntity student) {
 		 // id   查询 数据库里最后一个id
 	    int id = selectLastStudent().getStuId()+1;
 		String sqlString="insert into students(stu_id,stu_name,sex,mtel,qq,address,class_name) values ('"+id+"','"+student.getStuName()
 		  +"','"+student.getSex()+"','"+student.getMtel()+"','"+student.getQq()+"','"+student.getAddress()+"','"+student.getClassName()+"')";
 		 int result= DBManager.executeUpdate(sqlString);
 		return result;
 	}
      
}
